package engineer.serverapp.classes;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import engineer.serverapp.tools.Connector;

public class Automat {

	public String buyTrademark(int machineID, int trademarkID){
		
		try{
			Connection connection = new Connector().getConnection();
			Statement selectStatement = connection.createStatement();
			String query = "select * from automats.machines_trademarks where machine_id="+machineID +" and trademark_id="+trademarkID;
			ResultSet result = selectStatement.executeQuery(query);
			if(result.next()){
				int current_amount=result.getInt("current_amount");
				if(current_amount==0){
					return "Nie ma tego produktu!";
				}else{
				int wynik = current_amount - 1;
				selectStatement.executeUpdate("update automats.machines_trademarks set current_amount="+wynik +" where machine_id="+machineID + " and trademark_id="+trademarkID);
				return "Kupiłeś!";
				}
			}else{
				return "Brak produktu!";
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String refreshMachine(int machineID){
		try{
			
			Connection connection = new Connector().getConnection();
			Statement selectStatement = connection.createStatement();
			String query = "select machines.name, dictionary_trademarks.name, machines_trademarks.trademark_id, machines_trademarks.current_amount from automats.machines_trademarks join automats.machines on machines.id=machines_trademarks.machine_id join automats.dictionary_trademarks on dictionary_trademarks.id=machines_trademarks.trademark_id where machines.id="+machineID;
			ResultSet result = selectStatement.executeQuery(query);
			String dana="<table id='tabela'><tr><td><b>Nazwa Towaru</b></td><td><b>Ilość</b></td></tr>";
			while(result.next()){
				
				String trademark = result.getString("dictionary_trademarks.name");
				int current_amount = result.getInt("machines_trademarks.current_amount");
				dana +="<tr><td>"+trademark+"</td><td>"+current_amount+"</td></tr>";
				
			}
			dana=dana+"</table>";
			
			return dana;
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	public String sendRequest(int machineID){
		
		
		return null;
	}
	
	
	public String sendToDatabase(int machineID) {
		try {
			Connection connection = new Connector().getConnection();
			Statement selectStatement = connection.createStatement();
			String query = "select machines.name, machines.address, machines_trademarks.current_amount, dictionary_trademarks.name from automats.machines   join automats.machines_trademarks on machines.id=machines_trademarks.machine_id  join automats.dictionary_trademarks on dictionary_trademarks.id=machines_trademarks.trademark_id where machines.id="
					+ machineID;
			ResultSet rs = selectStatement.executeQuery(query);
			Statement selectStatement2 = connection.createStatement();
			
			while(rs.next()){
			
				String machineName = rs.getString("machines.name");
				String machineAddress = rs.getString("machines.address");
				int currentAmount = rs.getInt("machines_trademarks.current_amount");
				String trademarkName = rs.getString("dictionary_trademarks.name");
			
				
				ResultSet rs2 = selectStatement2.executeQuery("select count(*), old_amount, current_amount from automats.messages where automat_name='"+machineName+"' and trademark='"+trademarkName+"'");
				if(rs2.next()){
					
					if(rs2.getInt("count(*)")==0 && currentAmount<10){
				
						selectStatement2.executeUpdate("Insert into automats.messages (automat_name, automat_address, current_amount, trademark, old_amount) values ('"+machineName+"','"+machineAddress+"',"+currentAmount+",'"+trademarkName+"','"+currentAmount+"');");
					}else{
			
						if(currentAmount>10){
						
							selectStatement2.executeUpdate("Update automats.messages set current_amount="+currentAmount+", is_read=1, old_amount="+currentAmount+" where automat_name='"+machineName+"' and trademark='"+trademarkName+"'");
						}else{
						if(rs2.getInt("old_amount")>currentAmount){
					
							selectStatement2.executeUpdate("Update automats.messages set current_amount="+currentAmount+", is_read=0, old_amount="+currentAmount+" where automat_name='"+machineName+"' and trademark='"+trademarkName+"'");
					
					}else{
						System.out.println("Nie ma zmian");
					
					}
				}
				}
				}
				rs2.close();
			}
		
			rs.close();
			connection.close();
			return "Dane zostały wrzucone do wiadomości";
	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
